/*
 * Copyright (C) 2010 SJTU IST Lab., All Rights Reserved.
 */
package sjtu.ist.warehouse4ws.dynamicDS; 

import java.io.File;  
import java.sql.DriverManager;
import java.sql.PreparedStatement; 
import java.sql.Connection;
import java.sql.SQLException;  
import java.util.Iterator;  
  
import org.dom4j.Document;  
import org.dom4j.DocumentException;  
import org.dom4j.Element;  
import org.dom4j.io.SAXReader;  

import sjtu.ist.warehouse4ws.model.Role;

/**
 * @author Steve Jin
 * @version 1.0
 */


public class MetadataImport {
	
	public static String[] parseXml (Document document,String filename) throws DocumentException,SQLException{
		String metadata[]= new String[3];
		Document doc = null;
		SAXReader reader =new SAXReader();  
        doc=reader.read(new File(filename));  
        Element element=doc.getRootElement();  
        Iterator it1=element.elementIterator();  
        String IsNull=null;
        String IsKey=null;
        String database_name=null;
        String table_name=null;
        String column="";  
        while(it1.hasNext()){  
            Element element1=(Element)it1.next();  
            database_name=element1.attributeValue("name");            	
            Iterator it2=element1.elementIterator();
            while(it2.hasNext()){
            	Element element2=(Element)it2.next();
            	table_name=element2.attributeValue("name");
            	Iterator it3=element2.elementIterator();
            	while(it3.hasNext()){  
                    Element ele =(Element)it3.next();   
                    if (ele.attributeValue("Field") != null){
                    	
                    	if (ele.attributeValue("Null").equals("NO")){
                    		IsNull = "NOT NULL";
                    	}
                    	else IsNull = "NULL";
                    	if (ele.attributeValue("Key").equals("PRI")){
                    		IsKey = "PRIMARY KEY";
                    	}
                    	else IsKey = "";
                    	
                    column +=ele.attributeValue("Field")+" "+ele.attributeValue("Type")+" "+IsNull+" "+IsKey+",";  
                    }
                    else break;
                }  
                column=column.substring(0,column.length()-1);  

                //String dbsql="use "+database_name;
                //String sql="create table "+table_name+"_cust "+"( "+column+")";  
                //System.out.println(dbsql);
                //System.out.println(sql); 
            }
        }
        metadata[0] = database_name;
        metadata[1] = table_name;
        metadata[2] = column;
        
		return metadata;
	}	

	public static void executeSql(String filename,String prefix) throws DocumentException,SQLException{
		String[] metadata2 = parseXml(null,filename);	
        //加载MySql的驱动类   
		try{   
			Class.forName("com.mysql.jdbc.Driver") ;   
        	}catch(ClassNotFoundException e){   
        	System.out.println("找不到驱动程序类 ，加载驱动失败！");   
        	e.printStackTrace() ;   
        	} 
        
        //连接MySql数据库，用户名root,密码123456
        String url = "jdbc:mysql://localhost:3306/" ;    
        String username = "warehouse" ;   
        String password = "warehouse" ;   
        Connection conn = null;
        try{   
        	conn = DriverManager.getConnection(url , username , password ) ;
            }catch(SQLException se){   
            System.out.println("数据库连接失败！");   
            se.printStackTrace() ;   
            }    
		
        String dbsql="use "+metadata2[0];
        String sql="create table "+prefix+metadata2[1]+"( "+metadata2[2]+")";  
        System.out.println(dbsql);
        System.out.println(sql);
        PreparedStatement predb=conn.prepareStatement(dbsql); 
        PreparedStatement pre=conn.prepareStatement(sql);
        predb.execute();  
        pre.execute();
        
		return; 
	}
}